package cn.meye11.crm.mapper;

import cn.meye11.crm.common.DateUtils;
import cn.meye11.crm.common.RegexUtils;
import cn.meye11.crm.common.StringUtils;
import cn.meye11.crm.domain.entity.UserEntity;
import cn.meye11.crm.domain.query.UserQuery;
import org.apache.ibatis.jdbc.SQL;

/**
 * (User)表数据库访问层 SQL组合类
 * 
 * @author makejava
 * @since 2023-11-16 14:48:00
 */
 
public class UserProvider {

    public String list(UserQuery query) {

        SQL sql = new SQL();
        sql.SELECT("*");
        sql.FROM(UserEntity.Table + " u ");
        sql.WHERE(wheres(query));
        sql.ORDER_BY(query.getSort() + " " + query.getDirection());
        return sql.toString();
    }

    public String count(UserQuery query) {

        SQL sql = new SQL();
        sql.SELECT("count (id)");
        sql.FROM(UserEntity.Table + " u ");
        sql.WHERE(wheres(query));
        return sql.toString();
    }

    private String wheres(UserQuery query) {
        StringBuilder where = new StringBuilder();
        if (query != null) {
            // 不为空时，必须为逗号分割的数字才能查询
            String id = StringUtils.commaDelimitedTrimEmptyString(query.getId());
            if (StringUtils.isNotBlank(id) && RegexUtils.check(RegexUtils.Type.ONLY_NUMBER_OR_EMPTY, id)) {
                where.append("u.id in (" + id + ")").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getName())) {
                where.append("u.name = '" + query.getName() + "'").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getNameLike())) {
                where.append("u.name like '%" + query.getNameLike() + "%'").append(" and ");
            }
            String nameIn = StringUtils.toSqlInString(query.getNameIn());
            if (StringUtils.isNotBlank(nameIn)) {
                where.append("u.name in (" + nameIn + ")").append(" and ");
            }
            String nameNotIn = StringUtils.toSqlInString(query.getNameNotIn());
            if (StringUtils.isNotBlank(nameNotIn)) {
                where.append("u.name not in (" + nameNotIn + ")").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getUserName())) {
                where.append("u.user_name = '" + query.getUserName() + "'").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getUserNameLike())) {
                where.append("u.user_name like '%" + query.getUserNameLike() + "%'").append(" and ");
            }
            String userNameIn = StringUtils.toSqlInString(query.getUserNameIn());
            if (StringUtils.isNotBlank(userNameIn)) {
                where.append("u.user_name in (" + userNameIn + ")").append(" and ");
            }
            String userNameNotIn = StringUtils.toSqlInString(query.getUserNameNotIn());
            if (StringUtils.isNotBlank(userNameNotIn)) {
                where.append("u.user_name not in (" + userNameNotIn + ")").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getPassword())) {
                where.append("u.password = '" + query.getPassword() + "'").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getPasswordLike())) {
                where.append("u.password like '%" + query.getPasswordLike() + "%'").append(" and ");
            }
            String passwordIn = StringUtils.toSqlInString(query.getPasswordIn());
            if (StringUtils.isNotBlank(passwordIn)) {
                where.append("u.password in (" + passwordIn + ")").append(" and ");
            }
            String passwordNotIn = StringUtils.toSqlInString(query.getPasswordNotIn());
            if (StringUtils.isNotBlank(passwordNotIn)) {
                where.append("u.password not in (" + passwordNotIn + ")").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getMobile())) {
                where.append("u.mobile = '" + query.getMobile() + "'").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getMobileLike())) {
                where.append("u.mobile like '%" + query.getMobileLike() + "%'").append(" and ");
            }
            String mobileIn = StringUtils.toSqlInString(query.getMobileIn());
            if (StringUtils.isNotBlank(mobileIn)) {
                where.append("u.mobile in (" + mobileIn + ")").append(" and ");
            }
            String mobileNotIn = StringUtils.toSqlInString(query.getMobileNotIn());
            if (StringUtils.isNotBlank(mobileNotIn)) {
                where.append("u.mobile not in (" + mobileNotIn + ")").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getMail())) {
                where.append("u.mail = '" + query.getMail() + "'").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getMailLike())) {
                where.append("u.mail like '%" + query.getMailLike() + "%'").append(" and ");
            }
            String mailIn = StringUtils.toSqlInString(query.getMailIn());
            if (StringUtils.isNotBlank(mailIn)) {
                where.append("u.mail in (" + mailIn + ")").append(" and ");
            }
            String mailNotIn = StringUtils.toSqlInString(query.getMailNotIn());
            if (StringUtils.isNotBlank(mailNotIn)) {
                where.append("u.mail not in (" + mailNotIn + ")").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getAddress())) {
                where.append("u.address = '" + query.getAddress() + "'").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getAddressLike())) {
                where.append("u.address like '%" + query.getAddressLike() + "%'").append(" and ");
            }
            String addressIn = StringUtils.toSqlInString(query.getAddressIn());
            if (StringUtils.isNotBlank(addressIn)) {
                where.append("u.address in (" + addressIn + ")").append(" and ");
            }
            String addressNotIn = StringUtils.toSqlInString(query.getAddressNotIn());
            if (StringUtils.isNotBlank(addressNotIn)) {
                where.append("u.address not in (" + addressNotIn + ")").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getSex())) {
                where.append("u.sex = '" + query.getSex() + "'").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getSexLike())) {
                where.append("u.sex like '%" + query.getSexLike() + "%'").append(" and ");
            }
            String sexIn = StringUtils.toSqlInString(query.getSexIn());
            if (StringUtils.isNotBlank(sexIn)) {
                where.append("u.sex in (" + sexIn + ")").append(" and ");
            }
            String sexNotIn = StringUtils.toSqlInString(query.getSexNotIn());
            if (StringUtils.isNotBlank(sexNotIn)) {
                where.append("u.sex not in (" + sexNotIn + ")").append(" and ");
            }
            if (query.getBirthday() != null) {
                where.append("u.birthday = '" + query.getBirthday() + "'").append(" and ");
            }
            if (query.getRegistrationDate() != null) {
                where.append("u.registration_date = '" + query.getRegistrationDate()).append("' and ");
            }
            if (StringUtils.isNotBlank(query.getLabel())) {
                where.append("u.label = '" + query.getLabel() + "'").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getLabelLike())) {
                where.append("u.label like '%" + query.getLabelLike() + "%'").append(" and ");
            }
            String labelIn = StringUtils.toSqlInString(query.getLabelIn());
            if (StringUtils.isNotBlank(labelIn)) {
                where.append("u.label in (" + labelIn + ")").append(" and ");
            }
            String labelNotIn = StringUtils.toSqlInString(query.getLabelNotIn());
            if (StringUtils.isNotBlank(labelNotIn)) {
                where.append("u.label not in (" + labelNotIn + ")").append(" and ");
            }
            if (query.getStart() != null) {
                where.append("u.time >= '" + DateUtils.localDateTimeToStr(query.getStart()) + "'").append(" and ");
            }
            if (query.getEnd() != null) {
                where.append("u.time <= '" + DateUtils.localDateTimeToStr(query.getEnd()) + "'").append(" and ");
            }
        }
        where.append("u.del='0'");
        return where.toString();
    }
}
